Processing queries typically comprise at least two phases—compilation and execution. During compilation, one or more database server processes perform many functions, such as parsing the query, determining what table(s), column(s), data type(s), etc., are involved, determining whether an index may be used, and generating an execution plan. This process of compilation is typically referred to as a “hard parse.” The execution plan and much information utilized during the compilation stage are saved in a structure referred to as a cursor. During execution, one or more database server processes use the cursor to execute the query.
Occasionally, a query (that is issued against a database) is semantically equivalent to a previously issued query. Such a newly issued query is referred to hereinafter as the “new query.” A query that is issued previous to the new query is referred to hereinafter as a “previous query.”
Because so much work is involved in compilation, it would be beneficial for the new query to share the cursor that was generated for the previous query. Therefore, once a query is compiled, its cursor may be shared for subsequently issued queries that are (syntactically, or at least semantically) equivalent. Such using for a new query a cursor that was generated for a previous query is referred to as “cursor sharing.”
When a new query is issued to a database system that employs cursor sharing, a stored set of previously-generated cursors are searched to determine whether a previously-generated cursor can be used by the new query. Not only does cursor sharing avoid a hard parse each time an equivalent query is executed, cursor sharing may also reduce the amount of shared memory required to store the cursors.
Cursor sharing is also useful when a new query contains one or more bind variables. A bind variable is a substitution variable that is used in place of literals. In response to receiving the following three queries (Q1, Q2 and Q3) which contain different literals:
SELECT fname, lname, pcode FROM cust WHERE id=674;
SELECT fname, lname, pcode FROM cust WHERE id=234;
SELECT fname, lname, pcode FROM cust WHERE id=332;
a database server would perform three hard parses. However, the different literals may be replaced by a bind variable to produce the following query (Q4):
SELECT fname, lname, pcode FROM cust WHERE id=:id;
Query Q4 may be issued three times. By changing the value of the bind variable each time query Q4 is issued, a user may achieve the same results as would be produced by queries Q1, Q2 and Q3. However, the database server would only perform one hard parse Q4. The second and third time Q4 is issued, the cursor generated for the first execution of Q4 would be used for the subsequent executions of Q4. Therefore, the cursor corresponding to a previous query may be reused for a new query even though different values of the bind variables are specified with the new query execution.
However, when the bind values are significantly different, cursor sharing may be suboptimal. For example, assume that a database receives the following query Q5:
SELECT salaryFROM empWHERE job_id = :bind_var
Further assume that, the first time query Q5 is received, the bind value of bind variable “:bind_var” is “vice_president.” Based on the predicate “job_id=:bind_var,” the query optimizer determines that an index should be used to retrieve the rows of an employee table (i.e., “emp”) with the value of “vice_president” in the job_id column. The optimizer generates a cursor that includes an execution plan to execute the first query. In this example, only 0.01% of rows in the employee table satisfy the predicate.
The database server may then receive query Q5 again. However, the second time query Q5 is received, the bind value of bind variable “:bind_var” is “sales_rep.” If the database server executes the second instance of query Q5 using the cursor that was previously-generated for the first instance of query Q5, then the index on job_id will be used, even though “sales_rep” is a relatively frequent job title (e.g., 10% of the rows in the employee table). Such use of an index is very inefficient. If the second instance of query Q5 is compiled and executed as if cursor sharing was not an option, then the query optimizer might determine that a table scan of the employee table is more efficient than utilizing the index.
Therefore, a problem with cursor sharing for queries that include bind variables is that an execution plan that is optimal for one bind value may be sub-optimal for another bind value. Thus, there is a trade-off when using bind variables in a query. Although the benefits of cursor sharing include (1) less shared memory may be consumed to store the shared cursors and (2) fewer costly hard parses are performed, one significant drawback of cursor sharing is that unpredictable run-time performance may result (e.g., depending on the bind values used to build the cursor).
Under traditional cursor sharing a user (e.g., an administrator) must determine whether to always share cursors (by using bind variables) or never share cursors (by not using bind variables).
Cursor sharing has been extended to resolve a problem with traditional cursor sharing. The solution of such cursor sharing is to make cursor sharing bind-aware for a limited class of queries, i.e., queries with predicates using user-defined operators. In this approach, a bind variable is passed as an argument to a user-defined operator, such as in the user-defined operator CONTAINS in the following query:
SELECT avg(e.salary), d.department_nameFROM employees e, department dWHERE CONTAINS (e.job_id, :job)AND e.department_id = d_department_idGROUP BY d.department_name;
The user provides a cost and a selectivity function for the CONTAINS operator. The database server invokes the selectivity function of the operator based on the new bind value. The resulting selectivity value is compared to the selectivity value of an existing cursor. If the resulting selectivity value is within a range of the selectivity value of the existing cursor, then the selectivity range is extended to include the new value and the cursor is used.
The approaches described in this section are approaches that could be pursued, but not necessarily approaches that have been previously conceived or pursued. Therefore, unless otherwise indicated, it should not be assumed that any of the approaches described in this section qualify as prior art merely by virtue of their inclusion in this section.